package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.ContentInfo;
import cn.com.checne.model.domain.MsgInfo;
import cn.com.checne.model.domain.WebsiteInfo;
import cn.com.checne.model.dto.AnalyzerInfo;
import cn.com.checne.util.StringTools;

@Repository
public class MsgInfoDao {
	private static final Logger logger = Logger.getLogger(MsgInfoDao.class);

	public boolean synchMsg(MsgInfo msg) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "insert into tbl_msg (msg_id,msg_from,msg_to,msg_contentid_fk,msg_time,msg_status,status_time,is_read,read_time,is_used,used_time) values(?,?,?,?,?,?,?,?,?,?,?)";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i, StringTools.getUUID());
			i++;
			pstmt.setInt(i, msg.getMsgFrom());
			i++;
			pstmt.setInt(i, msg.getMsgTo());
			i++;
			pstmt.setString(i, msg.getContentId());
			i++;
			pstmt.setString(i, msg.getMsgTime());

			i++;
			pstmt.setInt(i, msg.getMsgStatus());
			i++;
			pstmt.setString(i, msg.getStatusTime());

			i++;
			pstmt.setInt(i, msg.getIsRead());
			i++;
			pstmt.setString(i, msg.getReadTime());

			i++;
			pstmt.setInt(i, msg.getIsUsed());
			i++;
			pstmt.setString(i, msg.getUsedTime());

			pstmt.execute();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<MsgInfo> getSubList(MsgInfo query, int start, int num) {
		List<MsgInfo> list = new ArrayList<MsgInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_msg where 1=1 ";
			if (query.getMsgFrom() > 0) {
				sql = sql + " and msg_from = ? ";
			}
			if (query.getMsgTo() > 0) {
				sql = sql + " and msg_to = ? ";
			}
			if (query.getMsgStatus() > 0) {
				sql = sql + " and msg_status = ? ";
			}
			if (query.getIsRead() > -1) {
				sql = sql + " and is_read = ? ";
			}
			if (query.getIsUsed() > -1) {
				sql = sql + " and is_used = ? ";
			}
			sql = sql + " order by msg_time desc limit ? , ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			if (query.getMsgFrom() > 0) {
				i++;
				pstmt.setInt(i, query.getMsgFrom());
			}
			if (query.getMsgTo() > 0) {
				i++;
				pstmt.setInt(i, query.getMsgTo());
			}
			if (query.getMsgStatus() > 0) {
				i++;
				pstmt.setInt(i, query.getMsgStatus());
			}
			if (query.getIsRead() > -1) {
				i++;
				pstmt.setInt(i, query.getIsRead());
			}
			if (query.getIsUsed() > -1) {
				i++;
				pstmt.setInt(i, query.getIsUsed());
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);

			ResultSet rs = pstmt.executeQuery();
			list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public int getCount(MsgInfo query) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_msg where 1=1 ";
			if (query.getMsgFrom() > 0) {
				sql = sql + " and msg_from = ? ";
			}
			if (query.getMsgTo() > 0) {
				sql = sql + " and msg_to = ? ";
			}
			if (query.getMsgStatus() > 0) {
				sql = sql + " and msg_status = ? ";
			}
			if (query.getIsRead() > -1) {
				sql = sql + " and is_read = ? ";
			}
			if (query.getIsUsed() > -1) {
				sql = sql + " and is_used = ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			if (query.getMsgFrom() > 0) {
				i++;
				pstmt.setInt(i, query.getMsgFrom());
			}
			if (query.getMsgTo() > 0) {
				i++;
				pstmt.setInt(i, query.getMsgTo());
			}
			if (query.getMsgStatus() > 0) {
				i++;
				pstmt.setInt(i, query.getMsgStatus());
			}
			if (query.getIsRead() > -1) {
				i++;
				pstmt.setInt(i, query.getIsRead());
			}
			if (query.getIsUsed() > -1) {
				i++;
				pstmt.setInt(i, query.getIsUsed());
			}

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("total");
			}
			pstmt.close();
			conn.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public List<MsgInfo> getListFromRS(ResultSet rs) {
		List<MsgInfo> list = new ArrayList<MsgInfo>();
		MsgInfo msg = null;
		WebsiteInfoDao websiteInfoDao = new WebsiteInfoDao();
		ContentInfoDao contentInfoDao = new ContentInfoDao();
		try {
			while (rs.next()) {
				msg = new MsgInfo();
				msg.setMsgId(rs.getString("msg_id"));
				msg.setMsgFrom(rs.getInt("msg_from"));
				msg.setMsgTo(rs.getInt("msg_to"));
				msg.setContentId(rs.getString("msg_contentid_fk"));
				msg.setMsgTime(rs.getString("msg_time"));

				msg.setMsgStatus(rs.getInt("msg_status"));
				msg.setStatusTime(rs.getString("status_time"));

				msg.setIsRead(rs.getInt("is_read"));
				msg.setReadTime(rs.getString("read_time"));

				msg.setIsUsed(rs.getInt("is_used"));
				msg.setReadTime(rs.getString("used_time"));

				WebsiteInfo websiteFrom = websiteInfoDao.getWebsite(msg
						.getMsgFrom());
				WebsiteInfo websiteTo = websiteInfoDao.getWebsite(msg
						.getMsgTo());
				ContentInfo contentInfo = contentInfoDao.getContentById(msg
						.getContentId());

				msg.setWebsiteFrom(websiteFrom);
				msg.setWebsiteTo(websiteTo);
				msg.setContentInfo(contentInfo);

				list.add(msg);
			}
			rs.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public boolean del(String msgId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "delete from tbl_msg where msg_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i, msgId);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<AnalyzerInfo> getAnalyzerList(MsgInfo query) {
		List<AnalyzerInfo> list = new ArrayList<AnalyzerInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			WebsiteInfoDao websiteInfoDao = new WebsiteInfoDao();
			List<WebsiteInfo> websiteInfoList = websiteInfoDao.getSubList(null,
					0, 2147483647);

			if (query.getWebsiteId() == 0) {
				AnalyzerInfo analyzer = null;
				for (WebsiteInfo website : websiteInfoList) {
					analyzer = new AnalyzerInfo();
					analyzer.setWebsiteId(website.getId());
					analyzer.setWebsiteInfo(website);
					query.setWebsiteId(website.getId());
					analyzer.setSendCount(getMsgAnalyzerDetailCount(1, query));
					analyzer.setRevCount(getMsgAnalyzerDetailCount(2, query));
					analyzer.setReadCount(getMsgAnalyzerDetailCount(3, query));
					analyzer.setUsedCount(getMsgAnalyzerDetailCount(4, query));
					list.add(analyzer);
				}
			} else {
				AnalyzerInfo analyzer = new AnalyzerInfo();
				analyzer.setWebsiteId(query.getWebsiteId());
				analyzer.setWebsiteInfo(websiteInfoDao.getWebsite(query
						.getWebsiteId()));
				analyzer.setSendCount(getMsgAnalyzerDetailCount(1, query));
				analyzer.setRevCount(getMsgAnalyzerDetailCount(2, query));
				analyzer.setReadCount(getMsgAnalyzerDetailCount(3, query));
				analyzer.setUsedCount(getMsgAnalyzerDetailCount(4, query));
				list.add(analyzer);
			}
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public int getMsgAnalyzerDetailCount(int type, MsgInfo query) {
		int total = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from  tbl_msg  where msg_from = ? and status_time >= ? and status_time <= ?  ";

			switch (type) {
			case 1:
				sql = "select count(1) as total from  tbl_msg  where msg_from = ? and status_time >= ? and status_time <= ?  ";
				break;
			case 2:
				sql = "select count(1) as total from  tbl_msg  where msg_to = ? and status_time >= ? and status_time <= ?  ";
				break;
			case 3:
				sql = "select count(1) as total from  tbl_msg  where msg_from = ? and is_read = 1 and read_time >= ? and read_time <= ?  ";
				break;
			case 4:
				sql = "select count(1) as total from  tbl_msg  where msg_from = ? and is_used = 1 and used_time >= ? and used_time <= ?  ";
				break;
			default:
				sql = "select count(1) as total from  tbl_msg  where msg_from = ? and status_time >= ? and status_time <= ?  ";
			}

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setInt(i, query.getWebsiteId());
			i++;
			pstmt.setString(i, query.getStartTime());
			i++;
			pstmt.setString(i, query.getEndTime());
			logger.info("[getMsgAnalyzerDetailCount] type=" + type + " "
					+ pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
			return total;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	public List<MsgInfo> getMsgAnalyzerDetailList(int type, MsgInfo query,
			int start, int num) {
		List<MsgInfo> list = new ArrayList<MsgInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from  tbl_msg  where msg_from = ? and status_time >= ? and status_time <= ? limit ? ,? ";

			switch (type) {
			case 1:
				sql = "select * from  tbl_msg  where msg_from = ? and status_time >= ? and status_time <= ? limit ? ,? ";
				break;
			case 2:
				sql = "select * from  tbl_msg  where msg_to = ? and status_time >= ? and status_time <= ? limit ? ,? ";
				break;
			case 3:
				sql = "select * from  tbl_msg  where msg_from = ? and is_read = 1 and read_time >= ? and read_time <= ? limit ? ,? ";
				break;
			case 4:
				sql = "select * from  tbl_msg  where msg_from = ? and is_used = 1 and used_time >= ? and used_time <= ? limit ? ,? ";
				break;
			default:
				sql = "select * from  tbl_msg  where msg_from = ? and status_time >= ? and status_time <= ? limit ? ,? ";
			}

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setInt(i, query.getWebsiteId());
			i++;
			pstmt.setString(i, query.getStartTime());
			i++;
			pstmt.setString(i, query.getEndTime());
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);
			logger.info("[getMsgAnalyzerDetailList] type=" + type + " "
					+ pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public boolean isAlwaysRead(int websiteId, String contentId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "update tbl_msg set is_read = 1 , read_time = ? where msg_from = ? and msg_contentid_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i,
					DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
			i++;
			pstmt.setInt(i, websiteId);
			i++;
			pstmt.setString(i, contentId);
			pstmt.executeUpdate();
			logger.info("isAlwaysRead" + pstmt.toString());
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean isAlwaysRead(String msgId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "update tbl_msg set is_read = 1 , read_time = ? where msg_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i,
					DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
			i++;
			pstmt.setString(i, msgId);
			pstmt.executeUpdate();
			logger.info("isAlwaysRead" + pstmt.toString());
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean isAlwaysUsed(int websiteId, String contentId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "update tbl_msg set is_used = 1 , used_time = ? where msg_from = ? and msg_contentid_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i,
					DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
			i++;
			pstmt.setInt(i, websiteId);
			i++;
			pstmt.setString(i, contentId);
			pstmt.executeUpdate();
			logger.info("isAlwaysUsed" + pstmt.toString());
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public MsgInfo getMsgInfoByMsgId(String msgId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_msg where msg_id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setString(i, msgId);
			ResultSet rs = pstmt.executeQuery();
			List<MsgInfo> list = getListFromRS(rs);
			MsgInfo msgInfo = (list != null) && (list.size() > 0) ? (MsgInfo) list
					.get(0) : new MsgInfo();
			pstmt.close();
			conn.close();
			return msgInfo;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new MsgInfo();
	}
}